const underscore = require('underscore');
const Bluebird = require('bluebird');
const util = require('../lib/util');
let getMysqlClient = require('../boot/mysql');

module.exports = {
    /**
     * @param {string} sql
     */
    build: function(sql, bind = {}) {
        let arr = [];
        for (let key in bind) {
            arr.push({
                k: key,
                v: bind[key]
            });
        }
        arr.sort((a, b) => {
            return a.k < b.k;
        });

        for (let item of arr) {
            let val = item.v;
            if (!underscore.isNumber(val)) val = "'" + util.addslashes(item.v) + "'";
            let re = new RegExp(`:${item.k}`, 'g');
            sql = sql.replace(re, val);
        }
        return sql;
    },

    query: async function(sql, bind = {}) {
        sql = this.build(sql, bind);        
        let client = await getMysqlClient();
        return new Bluebird((resolve, reject) => {
            client.query(sql, (err, res) => {
                client.release();
                if (err) reject(err);
                else resolve(res);
            });
        });
    },

    fetch: async function(sql, bind = {}) {
        sql += ' LIMIT 1';
        let res = await this.query(sql, bind);
        return res.length ? res : res[0];
    },

    /**
     * @param {string} table
     * @param {any} data
     */
    insert: async function(table, data) {
        let keys = Object.keys(data);
        let fields = '`' + keys.join('`, `') + '`';
        let vals = ':' + keys.join(', :');
        let sql = `INSERT INTO \`${table}\` (${fields}) VALUES (${vals})`;
        return await this.query(sql, data);
    },

    /**
     * @param {string} table
     * @param {array} data
     */
    insertAll: async function(table, data) {
        let keys = Object.keys(data[0]);
        let fields = '`' + keys.join('`, `') + '`';
        
        let vals = [];
        for (let item of data) {
            let arr = [];
            for (let k in item) {
                if (underscore.isNumber) arr.push( item[k] );
                else arr.push( util.addslashes(item[k]) );
            }
            let str = "'" + arr.join("', '") + "'";
            vals.push(`(${str})`);
        }
        let allvals = vals.join(', ');
        let sql = `INSERT INTO \`${table}\` (${fields}) VALUES ${allvals}`;
        return await this.query(sql);
    }
};